﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Modellayer;

namespace Dblayer
{
    public class DbPerson
    {
         SqlConnection con;

        public DbPerson()
        {
            con = new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dm76_4;Persist Security Info=True;User ID=DM76_4;Password=MaaGodt");
        }

        public void createPerson(Person p)
        {
            try
            {
                con.Open();
                string insert = @"insert into Person(PhoneNumber, Name, Email, Password, Persontype, loginId) values ('" + p.phoneNumber+ "','"+p.name+"','"+p.email+"','" +p.password+"','"+p.type+"','"+p.loginId+"')";
                SqlCommand myCommand = new SqlCommand(insert, con);
                myCommand.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        public Person findPerson(int PhoneNr)
        {
            Person p = new Person();
            SqlDataReader rdr = null;
            try
            {
                con.Open();
                string findQuery = @"select * from Person where PhoneNumber = '" + PhoneNr + "'";
                SqlCommand myCommand = new SqlCommand(findQuery, con);
                rdr = myCommand.ExecuteReader();
                while (rdr.Read())
                {
                    p.phoneNumber = int.Parse(rdr["PhoneNumber"].ToString());
                    p.name = rdr["Name"].ToString();
                    p.email = rdr["Email"].ToString();
                    p.password = rdr["Password"].ToString();
                    p.type = rdr["PersonType"].ToString();
                    p.loginId = rdr["LoginId"].ToString();
                }           

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
                p = null;
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }


                if (con != null)
                {
                    con.Close();
                }
                
            }

            return p;
        }

        public void UpdatePerson(Person p)
        {
            try
            {
                con.Open();
                String updateQuery = @"update person set name = '"+p.name+"', Email = '"+p.email+"', Password = '"+p.password+"', PersonType = '"+p.type+"', loginId = '"+p.loginId+"' where PhoneNumber = '"+p.phoneNumber+"'";
                SqlCommand myCommand = new SqlCommand(updateQuery, con);
                myCommand.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            } 
        }
    }       
}
